Time Series Data

Financial and economic data is mostly displayed and stored as time series. The time series data in the csv file is in long format

# libraries
library(data.table)
library(plotly)
## Loading required package: ggplot2
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:data.table':
## 
##     between, first, last
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(tidyr)

# read file
dt_prices <- fread("price_data.csv")
dt_prices
##        stock       date   price
##     1:  AMZN 2000-03-07   3.175
##     2:  AMZN 2000-01-03   4.469
##     3:  AMZN 2000-01-04   4.097
##     4:  AMZN 2000-01-05   3.488
##     5:  AMZN 2000-01-06   3.278
##    ---                         
## 17357:  MSFT 2022-12-23 238.730
## 17358:  MSFT 2022-12-27 236.960
## 17359:  MSFT 2022-12-28 234.530
## 17360:  MSFT 2022-12-29 241.010
## 17361:  MSFT 2022-12-30 239.820
# create wide
print("---> csv data in wide form")
## [1] "---> csv data in wide form"
dt_prices_wide <- dcast(dt_prices, date ~ stock, value.var = "price")
dt_prices_wide
##             date    AAPL   AMZN     MSFT
##    1: 2000-01-03   0.999  4.469  58.2813
##    2: 2000-01-04   0.915  4.097  56.3125
##    3: 2000-01-05   0.929  3.488  56.9063
##    4: 2000-01-06   0.848  3.278  55.0000
##    5: 2000-01-07   0.888  3.478  55.7188
##   ---                                   
## 5783: 2022-12-23 131.860 85.250 238.7300
## 5784: 2022-12-27 130.030 83.040 236.9600
## 5785: 2022-12-28 126.040 81.820 234.5300
## 5786: 2022-12-29 129.610 84.180 241.0100
## 5787: 2022-12-30 129.930 84.000 239.8200
# plot
fig1 <- plot_ly(dt_prices_wide, type = "scatter", mode = "lines") %>%
    add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
    add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
    add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
    layout(title = "Stock Price Series", yaxis=list(title="price"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig1

Converting To Return Series & Total Return Series

It may be difficult to compare the 3 price series above because the starting price is not the same. To overcome this, calculate the return series \(r_t\) for a stock and build a price series from a common base value.

The total return for a stock at time \(T\) is defined as

\[ TR(T) = 100 \times \Big[ \Pi_{i=1}^{t\leq T} \big( 1 + r_{t} \big)\Big]\]

Return at time \(t\) is

\[ r_t = \frac{p_t - p_{t-1}}{p_{t-1}}\]

Compare the Total Return series for the 3 stocks

dt_returns <- dt_prices_wide %>% 
  mutate(across(!date, ~ .x/lag(.x) -1)) %>% 
  drop_na()
  
fig2 <- plot_ly(dt_returns, type = "scatter", mode = "lines") %>%
    add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
    add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
    add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
    layout(title = "Stock Return Series", yaxis=list(title="returns"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig2
# calc total return
dt_cumulative_returns <- dt_returns %>% 
    mutate(across(!date, ~ 100*cumprod(1+.x)))

# plot
fig3 <- plot_ly(dt_cumulative_returns, type = "scatter", mode = "lines") %>%
    add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
    add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
    add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
    layout(title = "Stock Total Return Seriess", yaxis=list(title="returns"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig3

Questions

  1. Notice the difference once adjusted to the same reference?
  2. Which stock or combination would you have chosen?